using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;

namespace SubSonic
{
    public class ANSISqlGenerator : ISqlGenerator
    {
        internal SqlQuery query;

        public ANSISqlGenerator(SqlQuery q)
        {
            query = q;
        }

        private ANSISqlGenerator() {}

        public void SetInsertQuery(Insert q)
        {
            insert = q;
        }

        public virtual string QualifyTableName(TableSchema.Table tbl)
        {
            string result = "";
            result = string.Format("[{0}].[{1}]", tbl.SchemaName, tbl.Name);
            return result;
        }

        internal Insert insert;

        public string GetQualifiedSelect(TableSchema.Table table)
        {
            StringBuilder sb = new StringBuilder();
            bool isFirst = true;

            foreach(TableSchema.TableColumn tc in table.Columns)
            {
                if(!isFirst)
                    sb.Append(", ");

                sb.Append(tc.QualifiedName);
                isFirst = false;
            }

            string result = sb.ToString();

            return result;
        }

        public virtual string GenerateSelectColumnList()
        {
            StringBuilder sbColumns = new StringBuilder();
            foreach(TableSchema.Table tbl in query.FromTables)
            {
                string columnList = GetQualifiedSelect(tbl);
                sbColumns.AppendLine(columnList);
            }
            return sbColumns.ToString();
        }

        public TableSchema.TableColumn FindColumn(string columnName)
        {
            TableSchema.TableColumn result = null;
            foreach(TableSchema.Table t in query.FromTables)
            {
                foreach(TableSchema.TableColumn tc in t.Columns)
                {
                    if(Utilities.Utility.IsMatch(tc.ColumnName, columnName) || Utilities.Utility.IsMatch(tc.QualifiedName, columnName))
                    {
                        result = tc;
                        break;
                    }
                }
            }
            return result;
        }

        protected virtual string BuildAggregateCommands()
        {
            StringBuilder sb = new StringBuilder();
            bool isFirst = true;
            foreach(Aggregate agg in query.Aggregates)
            {
                if(!isFirst)
                    sb.Append(",");
                sb.Append(agg.ToString());
                isFirst = false;
            }
            return sb.ToString();
        }

        public virtual string GenerateGroupBy()
        {
            StringBuilder sb = new StringBuilder();

            bool isFirst = true;
            foreach(Aggregate agg in query.Aggregates)
            {
                if(agg.AggregateType == AggregateFunction.GroupBy)
                {
                    if(!isFirst)
                        sb.Append(",");
                    sb.Append(agg.ColumnName);
                    isFirst = false;
                }
            }

            string result = "";
            if(sb.Length > 0)
                result = SqlFragment.GROUP_BY + sb.ToString();

            return result;
        }

        public virtual string GenerateCommandLine()
        {
            StringBuilder sb = new StringBuilder();

            //start with the SqlCommand - SELECT, UPDATE, INSERT, DELETE
            sb.Append(query.SQLCommand);
            string columnList = "";
            if(query.Aggregates.Count > 0)
                columnList = BuildAggregateCommands();
            else
            {
                //set "TOP"
                sb.Append(query.TopSpec);

                //decide the columns
                if(query.SelectColumnList.Length == 0)
                    columnList = GenerateSelectColumnList();
                else
                {
                    StringBuilder sbCols = new StringBuilder();
                    //loop each column - 
                    //there n tables in the select list
                    //need to get the schema
                    //so for each column, loop the FromList until we find the column
                    bool isFirst = true;
                    foreach(object s in query.SelectColumnList)
                    {
                        //find the column
                        TableSchema.TableColumn c = FindColumn(s.ToString());
                        if(!isFirst)
                            sbCols.Append(", ");
                        if(c != null)
                        {
                            sbCols.Append(c.QualifiedName);
                            isFirst = false;
                        }
                        else
                        {
                            //just append it in - allowing for function calls
                            //or literals in the command line
                            sbCols.Append(s);
                        }
                    }
                    columnList = sbCols.ToString();
                }
            }
            sb.Append(columnList);

            if(query.Expressions.Count > 0)
            {
                //add in expression                
                foreach(string s in query.Expressions)
                {
                    sb.Append(",");
                    sb.Append(s);
                }
            }
            sb.AppendLine();

            string result = sb.ToString();
            return result;
        }

        public virtual string GenerateJoins()
        {
            string result = "";
            StringBuilder sb = new StringBuilder();

            if(query.Joins.Count > 0)
            {
                //build up the joins
                foreach(Join j in query.Joins)
                {
                    string joinType = Join.GetJoinTypeValue(j.Type);
                    string equality = " = ";
                    if(j.Type == Join.JoinType.NotEqual)
                        equality = " <> ";

                    sb.Append(joinType);
                    sb.Append(j.FromColumn.Table.QualifiedName);
                    if(j.Type != Join.JoinType.Cross)
                    {
                        sb.Append(" ON ");
                        sb.Append(j.ToColumn.QualifiedName);
                        sb.Append(equality);
                        sb.Append(j.FromColumn.QualifiedName);
                    }
                    sb.AppendLine("");
                }
            }
            result = sb.ToString();
            return result;
        }

        public virtual string GenerateFromList()
        {
            string result = "";
            StringBuilder sb = new StringBuilder();
            sb.Append(SqlFragment.FROM);

            bool isFirst = true;
            foreach(TableSchema.Table tbl in query.FromTables)
            {
                if(!isFirst)
                    sb.Append(",");
                sb.Append(tbl.QualifiedName);
                isFirst = false;
            }

            result = sb + Environment.NewLine;
            return result;
        }

        public virtual string GenerateConstraints()
        {
            string result = "";
            string whereOperator = SqlFragment.WHERE;

            if(query.Aggregates.Count > 0)
                whereOperator = SqlFragment.HAVING;

            StringBuilder sb = new StringBuilder();
            bool isFirst = true;

            int paramCount = 0;
            bool expressionIsOpen = false;
            bool isExpression = false;
            bool appendClause = true;

            foreach(Constraint c in query.Constraints)
            {
                isExpression = c.ColumnName.StartsWith("(");
                TableSchema.TableColumn col = FindColumn(c.ColumnName);
                string columnName = "";
                if(col != null)
                {
                    columnName = col.QualifiedName;
                    c.ParameterName = Utilities.Utility.PrefixParameter(col.ColumnName, query.provider);
                }
                else if(c.ColumnName != "##")
                {
                    //this could be an expression
                    string rawColumnName = c.ColumnName;
                    if(c.ColumnName.StartsWith("("))
                    {
                        rawColumnName = c.ColumnName.Replace("(", "");
                        expressionIsOpen = true;
                    }
                    c.ParameterName = Utilities.Utility.PrefixParameter(rawColumnName, query.provider);
                    columnName = c.ColumnName;
                }

                paramCount++;

                if(!isFirst)
                {
                    whereOperator = Enum.GetName(typeof(ConstraintType), c.Condition);
                    whereOperator = " " + whereOperator.ToUpper() + " ";
                }

                if(c.Comparison != Comparison.OpenParen && c.Comparison != Comparison.CloseParen)
                    sb.Append(whereOperator);

                if(c.Comparison == Comparison.BetweenAnd)
                {
                    sb.Append(columnName);
                    sb.Append(SqlFragment.BETWEEN);
                    sb.Append(c.ParameterName + "_start");
                    sb.Append(SqlFragment.AND);
                    sb.Append(c.ParameterName + "_end");
                }
                else if(c.Comparison == Comparison.In || c.Comparison == Comparison.NotIn)
                {
                    sb.Append(columnName);
                    if(c.Comparison == Comparison.In)
                        sb.Append(SqlFragment.IN);
                    else
                        sb.Append(SqlFragment.NOT_IN);

                    sb.Append("(");

                    if (c.InSelect != null) {

                        //create a sql statement from the passed-in select
                        string sql = c.InSelect.BuildSqlStatement();
                        sb.Append(sql);

                    } else {
                        //enumerate INs
                        IEnumerator en = c.InValues.GetEnumerator();
                        StringBuilder sbIn = new StringBuilder();
                        int i = 1;
                        while(en.MoveNext())
                        {
                            sbIn.Append(Utilities.Utility.PrefixParameter("in" + i.ToString() + ",", query.provider));
                            i++;
                        }
                        string inList = sbIn.ToString();
                        inList = Sugar.Strings.Chop(inList);
                        sb.Append(inList);
                    }


                    sb.Append(")");
                }
                else if(c.Comparison == Comparison.OpenParen)
                {
                    expressionIsOpen = true;
                    sb.Append("(");
                }
                else if(c.Comparison == Comparison.CloseParen)
                {
                    expressionIsOpen = false;
                    sb.Append(")");
                }
                else
                {
                    if(columnName.StartsWith("("))
                        expressionIsOpen = true;
                    if(c.ColumnName != "##")
                    {
                        sb.Append(columnName);
                        sb.Append(Constraint.GetComparisonOperator(c.Comparison));
                        sb.Append(c.ParameterName);
                    }
                }

                sb.AppendLine("");

                isFirst = false;
            }

            result = sb.ToString();
            //a little help...
            if(expressionIsOpen & !result.EndsWith(")"))
                result += ")";
            return result;
        }

        public virtual string GenerateOrderBy()
        {
            string result = "";
            StringBuilder sb = new StringBuilder();
            if(query.OrderBys.Count > 0)
            {
                sb.Append(SqlFragment.ORDER_BY);
                bool isFirst = true;
                foreach(string s in query.OrderBys)
                {
                    if(!isFirst)
                        sb.Append(",");
                    sb.Append(s);
                    isFirst = false;
                }
                sb.AppendLine();
            }
            return sb.ToString();
            ;
        }

        public virtual List<string> GetSelectColumns()
        {
            string delimitedColumns = "";
            List<string> result = new List<string>();
            string[] columnList = new string[0];
            string columns = "";

            if(query.SelectColumnList.Length == 0)
            {
                columns = GenerateSelectColumnList();
                columnList = columns.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);
                foreach(string s in columnList)
                    result.Add(s);
            }
            else
            {
                foreach(string s in query.SelectColumnList)
                    result.Add(s);
            }

            return result;
        }

        public virtual string GetPagingSqlWrapper()
        {
            return PAGING_SQL;
        }

        public virtual string BuildPagedSelectStatement()
        {
            string sql = "";
            string idColumn = GetSelectColumns()[0];
            string pkType = "";
            string sqlType = "";

            TableSchema.TableColumn idCol = FindColumn(idColumn);
            if(idCol != null)
            {
                if(Utilities.Utility.IsString(idCol))
                    pkType = "(" + idCol.MaxLength + ")";
                sqlType = Enum.GetName(typeof(SqlDbType), Utilities.Utility.GetSqlDBType(idCol.DataType));
                sqlType += pkType;
            }
            else
            {
                //assume it's an integer
                sqlType = Enum.GetName(typeof(SqlDbType), SqlDbType.Int);
            }

            string pagingFormat = PAGING_SQL;

            string select = GenerateCommandLine();
            string columnList = select.Replace("SELECT", "");
            string fromLine = GenerateFromList();
            string joins = GenerateJoins();
            string wheres = GenerateConstraints();

            //have to doctor the wheres, since we're using a WHERE in the paging
            //bits. So change all "WHERE" to "AND"
            string tweakedWheres = wheres.Replace("WHERE", "AND");
            string orderby = GenerateOrderBy();

            if(query.Aggregates.Count > 0)
                joins += GenerateGroupBy();

            //this uses SQL2000-compliant paging
            //the arguments are...
            //1 - id column - this is the PK or identifier
            //2 - from/join/where
            //3 - select/from/joins
            //4 - where/order by
            //5 - page index
            //6 - page size
            //7 - PK Type (using Utility.GetSqlDBType)

            sql = string.Format(pagingFormat, idColumn, fromLine + joins + wheres,
                select + fromLine + joins, tweakedWheres + orderby, query.CurrentPage, query.PageSize, sqlType);

            return sql;
        }

        public virtual string BuildSelectStatement()
        {
            string sql = "";

            if(query.PageSize > 0)
                sql = BuildPagedSelectStatement();
            else
            {
                //build the command string
                sql = GenerateCommandLine();
                sql += GenerateFromList();
                sql += GenerateJoins();

                if(query.Aggregates.Count > 0)
                    sql += GenerateGroupBy() + Environment.NewLine;

                sql += GenerateConstraints();
                sql += GenerateOrderBy();
            }
            //return
            return sql;
        }

        public virtual string BuildUpdateStatement()
        {
            StringBuilder sb = new StringBuilder();

            //cast it

            Update u = (Update)this.query;
            sb.Append(SqlFragment.UPDATE);
            sb.Append(u.FromTables[0].QualifiedName);

            //SET statements
            foreach(Setting s in u.SetStatements)
            {
                sb.Append(SqlFragment.SET);
                sb.Append(s.ColumnName);
                sb.Append("=");
                if(!s.IsExpression)
                    sb.AppendLine(s.ParameterName);
                else
                    sb.AppendLine(s.Value.ToString());
            }

            //wheres
            sb.Append(GenerateConstraints());

            return sb.ToString();
        }

        public string BuildInsertStatement()
        {
            StringBuilder sb = new StringBuilder();

            //cast it
            Insert i = this.insert;
            sb.Append(SqlFragment.INSERT_INTO);
            sb.Append(i.Table.QualifiedName);
            sb.Append("(");
            sb.Append(i.SelectColumns);
            sb.AppendLine(")");

            //if the values list is set, use that
            if(i.Inserts.Count > 0)
            {
                sb.Append(" VALUES (");
                bool isFirst = true;
                foreach(InsertSetting s in i.Inserts)
                {
                    if(!isFirst)
                        sb.Append(",");
                    if(!s.IsExpression)
                        sb.Append(s.ParameterName);
                    else
                        sb.Append(s.Value);
                    isFirst = false;
                }
                sb.AppendLine(")");
            }
            else
            {
                if(i.SelectValues != null)
                {
                    string selectSql = i.SelectValues.BuildSqlStatement();
                    sb.AppendLine(selectSql);
                }
                else
                    throw new SqlQueryException("Need to specify Values or a Select query to insert - can't go on!");
            }
            return sb.ToString();
        }

        public string BuildDeleteStatement()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SqlFragment.DELETE_FROM);
            sb.Append(query.FromTables[0].QualifiedName);

            sb.Append(GenerateConstraints());

            return sb.ToString();
        }


        #region Constants

        private const string PAGING_SQL =
            @"					
					DECLARE @Page int
					DECLARE @PageSize int

					SET @Page = {4}
					SET @PageSize = {5}

					SET NOCOUNT ON

					-- create a temp table to hold order ids
					DECLARE @TempTable TABLE (IndexId int identity, _keyID {6})

					-- insert the table ids and row numbers into the memory table
					INSERT INTO @TempTable
					(
					  _keyID
					)
					SELECT 
						{0}
					    {1}

					-- select only those rows belonging to the proper page
					    {2}
					INNER JOIN @TempTable t ON {0} = t._keyID
					WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                    {3}
                    ";

        #endregion
    }
}
